﻿/******************************************************************************
'Description:发动机总体事业部检验任务看板类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhunan
'create Date:2019.01.05
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.IO;

namespace ReportBusiness
{
    public class ZSCheckMissionLookBoardBusiness
    {
        public uMESReportOracleDAL dal = new uMESReportOracleDAL();
        private uMESCommonMethod uMesComm = new uMESCommonMethod();

        public DataTable GetFactoryInfo(string factoryid)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT f.factoryid,f.factoryname FROM factory f WHERE f.factoryid ='{0}'", factoryid);
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        //查询待检验信息 add zhunan 2017-10-12
        public DataTable GetZSCheckMessionLookBoardInfo(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("engineNo");
            dt.Columns.Add("partNo");
            dt.Columns.Add("partName");
            dt.Columns.Add("teamname");
            dt.Columns.Add("checkedQty");
            dt.Columns.Add("notCheckedQty");
            dt.Columns.Add("tijiaoDate");
            dt.Columns.Add("waiteTime");

            for (int i = 0; i < 10; i++)
            {
                DataRow dr = dt.NewRow();
                dr["engineNo"] = "FDJ000"+i;
                dr["partNo"] = "partNo-00" + i;
                dr["partName"] = "部件"+i;
                dr["teamname"] = "班组"+i;
                dr["checkedQty"] = 10;
                dr["notCheckedQty"] = 10;
                dr["tijiaoDate"] = DateTime.Now.ToShortDateString();
                dr["waiteTime"] = 0;
                dt.Rows.Add(dr);
            }

            pageCount = 1;
            rowCount = 10;
            return dt;
        }

        // 获取本月的检验完成数
        public string GetFinishedCountMonth()
        {
            string sql = @"
                        SELECT ep.containerno,ep.containername
                          FROM ENGINEPARTPLAN EP
                         WHERE TO_CHAR(EP.FINISHDATE, 'yyyymm') = TO_CHAR(SYSDATE, 'yyyymm')
                           AND (NOT (EP.CONTAINERNO IS NULL))
                        ";
            DataTable dt = dal.GetDataTable(sql);
            return dt.Rows.Count.ToString();   
        }

        //获取本月的检验超期完成数,本月完工
        public string GetOverdueFinishedCountMonth()
        {
            string sql = @"
                        SELECT DISTINCT  csw.containerid  FROM containerstepworkreport csw
                         INNER JOIN containercheckinfo cci  ON cci.containerstepworkreportid=csw.containerstepworkreportid and cci.isuse='0'
                         LEFT JOIN enginepartplan ep  ON NVL(ep.enginecontainerid,ep.containerid)=cci.containerid
                         WHERE csw.isuse='0' and TO_CHAR(EP.FINISHDATE, 'yyyymm') = TO_CHAR(SYSDATE, 'yyyymm') AND cci.createdate>csw.createdate+1
                        ";
            DataTable dt = dal.GetDataTable(sql);
            return dt.Rows.Count.ToString();
        }

        //获取本月的检验未完成数据(本月已经报工但没有检验的记录,多笔报工取等待期最大的一笔)
        public DataTable GetUnFinishedDataMonth(Dictionary<string, object> para, out string UnFinishedCount, out int pagecount, out int rowCount)
        {
            string sql = @"
                         SELECT u.attributevalue,ep.containerno,cc.productdescription,
                         wfs.workflowstepname,csw.createdate,round(SYSDATE-csw.createdate)*24 SkipHour,t.teamname,csw.containerid
                         FROM containerstepworkreport csw
                         LEFT JOIN containercheckinfo cci  ON cci.containerstepworkreportid=csw.containerstepworkreportid and cci.isuse='0' 
                         INNER JOIN enginepartplan ep  ON NVL(ep.enginecontainerid,ep.containerid)=csw.containerid
                         LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid
                         LEFT JOIN containercurrentinfo cc ON cc.containerid=csw.containerid
                         LEFT JOIN team t ON csw.Teamid=t.teamid
                         LEFT JOIN Userattribute u ON u.parentid = ep.mfgorderid AND u.userattributename = '发动机'
                         WHERE  csw.isuse='0' and ep.usecompleted='0' and cci.containerstepworkreportid IS NULL                          
                        ";
            sql += string.Format(" and csw.factoryid='{0}' ", para["factoryid"]);
            sql += " ORDER BY csw.containerid,createdate asc";
            DataTable dtDetail = dal.GetDataTable(sql);
            dtDetail.Columns.Add("WaitCheck");
            dtDetail.Columns.Add("HasCheck");

            string Cid=string.Empty;
            //删除记录中ContainerId重复的记录，只保留一笔
            string unFinishedCount="0";
            string FinishedStepCount = "0";             
            for(int iCount=dtDetail.Rows.Count-1;iCount>=0;iCount--)
            {
                if (Cid == dtDetail.Rows[iCount]["containerid"].ToString())
                {
                    dtDetail.Rows.RemoveAt(iCount);
                }
                else
                {
                    Cid = dtDetail.Rows[iCount]["containerid"].ToString();
                    FinishedStepCount = GetFinishedStepCount(Cid, out unFinishedCount);
                    dtDetail.Rows[iCount]["WaitCheck"] = unFinishedCount;
                    dtDetail.Rows[iCount]["HasCheck"] = FinishedStepCount;
                }               

            }
            dtDetail.Columns.Remove("containerid");
            dtDetail.AcceptChanges();          

            UnFinishedCount = dtDetail.Rows.Count.ToString();
            rowCount = dtDetail.Rows.Count;
            if(rowCount%(Convert.ToInt32(para["pageSize"]))==0)
            {
                pagecount = rowCount / (Convert.ToInt32(para["pageSize"]));

            }
            else
                pagecount=rowCount/(Convert.ToInt32(para["pageSize"]))+1;

            //将DataTable转换为分页Datatable
            DataTable dt = uMesComm.ConvertDataTableToPageDt(dtDetail, para);
            return dt;           
        }

        //获取已检和待检(已报工未检验)的工序工步记录 Add by Yuxj20190107
        public string GetFinishedStepCount(string Containerid, out string unFinishedCount)
        {
            string sql = @"
                        SELECT csw.containerid,csw.containerstepworkreportid,cc.containercheckinfoid 
                        FROM containerstepworkreport csw
                        LEFT JOIN containercheckinfo cc ON csw.containerstepworkreportid=cc.containerstepworkreportid and cc.isuse='0'
                        WHERE csw.isuse='0'
                        ";
            sql += string.Format(" and csw.containerid='{0}' ", Containerid);
            DataTable dtDetail = dal.GetDataTable(sql);
            int i_total = dtDetail.Rows.Count; //一定大于0
            DataView dv = dtDetail.DefaultView;
            dv.RowFilter = "containercheckinfoid is null";
            dtDetail = dv.ToTable();
            unFinishedCount = dtDetail.Rows.Count.ToString();
            return (i_total - dtDetail.Rows.Count).ToString(); 
        }
    }
}
